package co.th.bjc.md.helper.impl;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import co.th.bjc.md.dao.Images;
import co.th.bjc.md.dao.ProductImage;
import co.th.bjc.md.helper.entries.HelperDatabse;

public class ImagesHelper extends HelperDatabse{
	
	public ProductImage getImageByImgId(Integer prodImgId) throws Exception{
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		ProductImage prodImg = null;
		try {
			con = getConnection();
			stmt = con.prepareStatement(FindProductImageByProdImgId);
			stmt.setInt(1, prodImgId); 
			
			rs = stmt.executeQuery();
			if (rs.next()){
				prodImg = new ProductImage();
				prodImg.setProductCode(rs.getBigDecimal("product_code"));
				prodImg.setProdImgCode(rs.getInt("image_id"));
				prodImg.setProdImgContent(rs.getBinaryStream("image_data"));
				
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
			if (con != null) con.close();
		}
		return prodImg;
	}
	
	public Images getImagesByImgId(Integer imageId) throws Exception{
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		Images image = null;
		try {
			con = getConnection();
			stmt = con.prepareStatement(IMAGES.FIND_BY_IMAGE_ID);
			stmt.setInt(1, imageId); 
			
			rs = stmt.executeQuery();
			if (rs.next()){
				image = new Images();
				image.setImageId(rs.getInt("image_id"));
				image.setImageName(rs.getString("image_name"));
				image.setImageData(rs.getBinaryStream("image_data"));
				image.setProductCode(rs.getBigDecimal("product_code"));
				image.setCreateBy(rs.getString("create_by"));
				image.setCreateDate(new Date(rs.getTimestamp("create_date").getTime()));
				image.setUpdateBy(rs.getString("update_by"));
				image.setUpdateDate(new Date(rs.getTimestamp("update_date").getTime()));
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
			if (con != null) con.close();
		}
		return image;
	}
	
	public List<Images> getImagesByProductCode(BigDecimal productCode) throws Exception{
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		Images image = null;
		List<Images> images = new ArrayList<Images>(0);
		try {
			con = getConnection();
			stmt = con.prepareStatement(IMAGES.FIND_BY_PRODUCT_CODE);
			stmt.setInt(1, productCode.intValue()); 
			
			rs = stmt.executeQuery();
			while (rs.next()){
				image = new Images();
				image.setImageId(rs.getInt("image_id"));
				image.setImageName(rs.getString("image_name"));
				image.setImageData(rs.getBinaryStream("image_data"));
				image.setProductCode(rs.getBigDecimal("product_code"));
				images.add(image);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
			if (con != null) con.close();
		}
		return images;
	}
	
	public Images addImage(Connection conn, Images input) throws Exception {
		try {
			if (conn==null){
				conn = getConnection();
				conn.setAutoCommit(true);
			}

			input = insert(conn, input);
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return input;
	}
	
	public List<Images> addImage(Connection conn, List<Images> input) throws Exception {
		boolean isConnNull = false;
		try {
			if (conn==null){
				conn = getConnection();
				isConnNull = true;
				conn.setAutoCommit(true);
			}
			for(Images image : input){
				if(image.getImageId() != null){
					update(conn, image);
				}else {
					image = insert(conn, image);
				}
				
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		}finally{
			if (conn != null && isConnNull){
				conn.close();
			}
		}
		return input;
	}
	
	public boolean delete(Connection conn, Integer imgId) throws Exception {
		PreparedStatement stmt = null;
		try {			
			if (conn==null){
				conn = getConnection();
				conn.setAutoCommit(true);
			}
			stmt = conn.prepareStatement(IMAGES.DELETE);
			stmt.setInt(1, imgId);

			int record = stmt.executeUpdate();
			return record > 0 ? true : false;
		} catch (Exception ex) {
			ex.printStackTrace();
			return false;
		} finally {
			if (stmt != null)
				stmt.close();
		}
	}
	
	public boolean update(Connection conn, List<Images> input) throws Exception {
		boolean isConnNull = false;
		try {
			if (conn==null){
				conn = getConnection();
				isConnNull = true;
				conn.setAutoCommit(true);
			}

			for(Images image : input){
				if (!edit(conn, image)) return false;
			}
			return true;
		} catch (Exception ex) {
			ex.printStackTrace();
			return false;
		}finally{
			if (conn != null && isConnNull){
				conn.close();
			}
		}
	}
	
	public boolean update(Connection conn, Images input) throws Exception {
		try {
			if (conn==null){
				conn = getConnection();
				conn.setAutoCommit(true);
			}

			return edit(conn, input);
		} catch (Exception ex) {
			ex.printStackTrace();
			return false;
		}
	}
	
	/* ========================================================================================= */
	
	synchronized Images insert(Connection conn, Images input) throws Exception {
		PreparedStatement stmt = null;
		try {			
			stmt = conn.prepareStatement(IMAGES.INSERT);
			//stmt.setInt(1, input.getImageId().intValue());
			stmt.setString(1, input.getImageName());
			stmt.setBlob(2, input.getImageData(), input.getImageData().available());
			stmt.setBigDecimal(3, input.getProductCode());
			stmt.setString(4, input.getCreateBy());
			stmt.setDate(5, new java.sql.Date(input.getCreateDate().getTime()));
			stmt.setString(6, input.getUpdateBy());
			stmt.setDate(7, new java.sql.Date(input.getCreateDate().getTime()));

			stmt.executeUpdate();

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (stmt != null)
				stmt.close();
		}
		return input;
	}
	
	synchronized boolean edit(Connection conn, Images input) throws Exception {
		PreparedStatement stmt = null;
		try {
			stmt = conn.prepareStatement(IMAGES.UPDATE);
			stmt.setString(1, input.getImageName());
			stmt.setBlob(2, input.getImageData(), input.getImageData().available());
			stmt.setString(3, input.getCreateBy());
			stmt.setDate(4, new java.sql.Date(input.getCreateDate().getTime()));
			stmt.setString(5, input.getUpdateBy());
			stmt.setDate(6, new java.sql.Date(input.getCreateDate().getTime()));
			stmt.setInt(7, input.getImageId());

			int record = stmt.executeUpdate();
			return record > 0 ? true : false;
		} catch (Exception ex) {
			ex.printStackTrace();
			return false;
		} finally {
			if (stmt != null)
				stmt.close();
		}
	}
	
	synchronized Integer getSequence(Connection conn) throws Exception{
		PreparedStatement stmt = null;
		ResultSet rs = null;
		Integer sequence = null;
		try {
			stmt = conn.prepareStatement(IMAGES.SEQUENCE);
			rs = stmt.executeQuery();
			if (rs.next()){
				sequence = rs.getInt(1) + 1;
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
		}
		return sequence;
	}
}